CREATE OR REPLACE PACKAGE BODY "PKG_BB"
AS

    PROCEDURE get_student_grades(p_result   OUT SYS_REFCURSOR,
                                 p_teacher_id NUMBER,
                                 p_student_id NUMBER)
    AS
        v_admin       NUMBER;
        v_lastfirst   pa_student.lastfirst%TYPE;
        v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;
    BEGIN

        SELECT is_admin
        INTO v_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        SELECT MAX(s.lastfirst)
        INTO v_lastfirst
        FROM pa_student s JOIN pa_enrollment e ON s.student_id=e.student_id
                          JOIN pa_section se ON e.section_id=se.section_id
        WHERE se.teacher_id=p_teacher_id
          AND s.student_id=p_student_id
          AND se.semester_id=v_semester_id;


        OPEN p_result FOR
        SELECT s.student_id,
               s.lastfirst AS student_name,
               c.course_name AS CLASS,
               '65.5%' AS GRADE,
               se.section_id,
               1 AS last_mailed_id, TO_DATE('1/2/2011','MM/DD/YYYY') AS last_mailed_date
        FROM pa_student s JOIN pa_enrollment e ON s.student_id=e.student_id
                          JOIN pa_section se ON e.section_id=se.section_id
                          JOIN pa_course c ON c.course_id = se.course_id
        WHERE s.student_id=p_student_id
          AND se.semester_id=v_semester_id
          AND (v_admin=1 OR v_lastfirst IS NOT NULL)
        ORDER BY CLASS;

    END get_student_grades;

    PROCEDURE get_section_grades(p_result   OUT SYS_REFCURSOR,
                                 p_teacher_id NUMBER,
                                 p_student_id NUMBER,
                                 p_section_id NUMBER,
                                 p_grade_to_date OUT VARCHAR2)
    AS
        v_admin       NUMBER;
        v_lastfirst   pa_student.lastfirst%TYPE;
        v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;

    BEGIN

        SELECT '120.35%'
        INTO p_grade_to_date
        FROM dual;

        SELECT is_admin
        INTO v_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        SELECT MAX(s.lastfirst)
        INTO v_lastfirst
        FROM pa_student s JOIN pa_enrollment e ON s.student_id=e.student_id
                          JOIN pa_section se ON e.section_id=se.section_id
        WHERE se.teacher_id=p_teacher_id
          AND s.student_id=p_student_id
          AND se.semester_id=v_semester_id;


        OPEN p_result FOR
        SELECT s.lastfirst AS student_name,
               c.course_name AS item_name,
               TO_DATE(NULL) AS due_date,
               TO_DATE(NULL) AS last_student_activity,
               TO_DATE(NULL) AS last_teacher_activity,
               '20.00' AS GRADE,
               20 AS points_possible,
               'Thanks for getting this' AS COMMENTS,
               se.section_id
        FROM pa_student s JOIN pa_enrollment e ON s.student_id=e.student_id
                          JOIN pa_section se ON e.section_id=se.section_id
                          JOIN pa_course c ON c.course_id = se.course_id
        WHERE s.student_id=p_student_id
          AND se.semester_id=v_semester_id
          AND (v_admin=1 OR v_lastfirst IS NOT NULL)
        ORDER BY item_name;

    END get_section_grades;

    PROCEDURE blast_off_report (p_result   OUT SYS_REFCURSOR,
                                p_teacher_id NUMBER,
                                p_student_name VARCHAR2 DEFAULT NULL,
                                p_section_id NUMBER DEFAULT NULL)
    AS

            v_admin       NUMBER;
            v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;

    BEGIN

        SELECT is_admin
        INTO v_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        OPEN p_result FOR
        SELECT s.student_id, s.lastfirst,
               c.course_name, sem.firstday AS start_day, sem.lastday AS end_date,
               55 AS num_of_logins,
               TO_DATE('10/11/2010','MM/DD/YYYY') AS first_date_accessed,
               TO_DATE('1/2/2011','MM/DD/YYYY') AS last_date_accessed,
               1 AS days_since_last_access,
               TO_DATE('1/2/2011','MM/DD/YYYY') AS last_date_submitted,
               0 AS days_since_submitted,
               35 AS total_time_in_min_prev_week,
               365 AS total_time_in_minutes,
               90 AS cumulative_grade,
               82 AS percent_complete,
               89 AS grade_to_date
        FROM pa_student s JOIN pa_enrollment e ON s.student_id=e.student_id
                          JOIN pa_section se ON e.section_id=se.section_id
                          JOIN pa_course c ON se.course_id=c.course_id
                          JOIN pa_semester sem ON se.semester_id = sem.semester_id
        WHERE s.lastfirst LIKE '%' || p_student_name || '%'
          AND v_admin = 1
          AND (CASE WHEN se.section_id IS NOT NULL THEN 1
                    WHEN se.section_id = p_section_id THEN 1
                    ELSE 0
              END) = 1
         /* AND se.section_id = NVL(p_section_id,se.section_id)*/
          AND se.semester_id=v_semester_id
        ORDER BY s.lastfirst;

    END blast_off_report;

    PROCEDURE teacher_tracking_report (p_result   OUT SYS_REFCURSOR,
                                       p_teacher_id NUMBER,
                                       p_teacher_name VARCHAR2 DEFAULT NULL,
                                       p_section_id NUMBER DEFAULT NULL)
    AS

            v_admin       NUMBER;
            v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;

    BEGIN

        SELECT is_admin
        INTO v_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        IF v_admin IS NOT NULL AND v_admin = 1 THEN

            OPEN p_result FOR
            SELECT t.teacher_id, t.lastfirst AS teachr_name,
                   c.course_name, sem.lastday AS last_course_access,
                   55 AS total_student_count,
                   4439 AS total_teacher_online_time,
                   34.15 AS total_teacher_online_prev_week,
                   65 AS ungraded_item_count,
                   TO_DATE('10/11/2011','MM/DD/YYYY') AS first_date_accessed
            FROM pa_teacher t JOIN pa_section se ON t.teacher_id = se.teacher_id
                              JOIN pa_course c ON se.course_id=c.course_id
                              JOIN pa_semester sem ON se.semester_id = sem.semester_id
            WHERE t.lastfirst LIKE '%' || p_teacher_name || '%'
              AND (CASE WHEN se.section_id IS NOT NULL THEN 1
                        WHEN se.section_id = p_section_id THEN 1
                        ELSE 0
                  END) = 1
             /* AND se.section_id = NVL(p_section_id,se.section_id)*/
              AND sem.semester_id=v_semester_id
            ORDER BY t.lastfirst, c.course_name;

         END IF;

    END teacher_tracking_report;

    PROCEDURE rainbow_report (p_result   OUT SYS_REFCURSOR,
                              p_teacher_id NUMBER,
                              p_student_name VARCHAR2 DEFAULT NULL,
                              p_section_id NUMBER DEFAULT NULL)
    AS

            v_admin       NUMBER;
            v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;

    BEGIN

        SELECT is_admin
        INTO v_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        OPEN p_result FOR
        SELECT s.lastfirst AS student_name, s.student_id,
               c.course_name,
               5 AS overdue_assignments,
               55.55 AS grade_to_date,
               TO_DATE('2/13/2011','MM/DD/YYYY') AS last_assignment_date,
               TO_DATE('1/24/2011','MM/DD/YYYY') AS first_date_accessed,
               s.email, s.phone,
               NVL(x.father, x.mother) parent_name,
               x.email_guard_pers parent_email,
               COALESCE(x.fatherdayphone,x.father_home_phone,x.motherdayphone,x.mother_home_phone) parent_phone
        FROM pa_student s LEFT JOIN pa_student_ext x ON s.student_id=x.student_id
                               JOIN pa_enrollment e ON s.student_id=e.student_id
                               JOIN pa_section se ON e.section_id=se.section_id
                               JOIN pa_course c ON se.course_id=c.course_id
                               JOIN pa_semester sem ON se.semester_id = sem.semester_id
        WHERE s.lastfirst LIKE '%' || p_student_name || '%'
          AND v_admin = 1
          AND (CASE WHEN se.section_id IS NOT NULL THEN 1
                    WHEN se.section_id = p_section_id THEN 1
                    ELSE 0
              END) = 1
         /* AND se.section_id = NVL(p_section_id,se.section_id)*/
          AND se.semester_id=v_semester_id
        ORDER BY s.lastfirst;

    END rainbow_report;

    PROCEDURE get_all_courses (p_result   OUT SYS_REFCURSOR)

    AS

        v_semester_id NUMBER:=PKG_SEMESTER.semester_by_date;

    BEGIN

                OPEN p_result FOR
                SELECT c.course_number code,
                       CASE WHEN COUNT(*) OVER(PARTITION BY c.COURSE_ID)>1 THEN
                                 c.course_name || ' - ' || s.section_number
                            ELSE c.course_name
                       END section_title,
                       s.section_id
                FROM pa_course c JOIN pa_section s ON s.course_id=c.course_id
                WHERE s.semester_id=v_semester_id
                ORDER BY section_title;

    END get_all_courses;

    PROCEDURE EMAIL_DELIVERY_REPORT (p_result      OUT SYS_REFCURSOR,
                                                   p_from        DATE,
                                                   p_to          DATE,
                                                   p_section_id  NUMBER)
    AS

    BEGIN

            OPEN p_result FOR
            SELECT e.date_sent, COUNT(e.id) AS totals_number_of_emails,
                   COUNT(CASE WHEN e.deliv_status = 1 THEN 1 END) AS Delivered,
                   COUNT(CASE WHEN e.deliv_status = 0 THEN 1 END) AS Failed
            FROM wpr_email e JOIN pa_enrollment en ON e.student_id = en.student_id
                             JOIN pa_section se ON en.section_id = se.section_id
            WHERE e.date_sent BETWEEN p_from AND p_to
              AND en.section_id = NVL(p_section_id,en.section_id)
            GROUP BY e.date_sent
            ORDER BY e.date_sent;

    END EMAIL_DELIVERY_REPORT;

    PROCEDURE EMAIL_DELIVERY_DETAILS (p_result   OUT SYS_REFCURSOR,
                                      p_date     DATE,
                                      p_status   NUMBER)  -- NULL for 'ALL', 1 for 'SUCCESS', 0 for 'FAILED'

    AS

    BEGIN

            OPEN p_result FOR
            SELECT e.STUDENT_ID, s.LASTFIRST,
               MAX(CASE WHEN d.EMAIL_TYPE = 'BANV' THEN d.EMAIL_ADDRESS END) AS banv_email,
               MAX(CASE WHEN d.EMAIL_TYPE = 'BANV' THEN d.DELIV_STATUS END) AS banv_status,
               MAX(CASE WHEN d.EMAIL_TYPE = 'PERSONAL' THEN d.EMAIL_ADDRESS END) AS personal_email,
               MAX(CASE WHEN d.EMAIL_TYPE = 'PERSONAL' THEN d.DELIV_STATUS END) AS personal_status,
               MAX(CASE WHEN d.EMAIL_TYPE = 'PARENT' THEN d.EMAIL_ADDRESS END) AS parent_email,
               MAX(CASE WHEN d.EMAIL_TYPE = 'PARENT' THEN d.deliv_status END) AS parent_status
            FROM wpr_email e LEFT JOIN wpr_details d ON e.ID = d.WPR_ID
                                  JOIN pa_student s ON e.STUDENT_ID = s.STUDENT_ID
            WHERE e.DATE_SENT = p_date
              AND d.DELIV_STATUS = NVL(p_status,d.DELIV_STATUS)
            GROUP BY e.STUDENT_ID, s.LASTFIRST
            ORDER BY e.STUDENT_ID;

    END email_delivery_details;

    PROCEDURE STUDENT_EMAIL_HISTORY (p_result   OUT SYS_REFCURSOR,
                                     p_student_id   NUMBER)

    AS
            v_firstday    DATE;
            v_lastday     DATE;

    BEGIN

            SELECT s.FIRSTDAY, s.LASTDAY
            INTO v_firstday, v_lastday
            FROM PA_SEMESTER S JOIN PA_TERM T ON S.TERM_ID=T.TERM_ID
            WHERE s.semester_id=(SELECT MIN(semester_id)
                                 FROM pa_semester
                                 WHERE lastday>TRUNC(SYSDATE));

            OPEN p_result FOR
            SELECT s.LASTFIRST,
                   e.DATE_SENT AS delivery_date,
                   MAX(CASE WHEN d.EMAIL_TYPE = 'BANV' THEN d.DELIV_STATUS END) AS banv_status,
                   MAX(CASE WHEN d.EMAIL_TYPE = 'PERSONAL' THEN d.DELIV_STATUS END) AS personal_status,
                   MAX(CASE WHEN d.EMAIL_TYPE = 'PARENT' THEN d.deliv_status END) AS parent_status
            FROM wpr_email e LEFT JOIN wpr_details d ON e.ID = d.WPR_ID
                                  JOIN pa_student s ON e.STUDENT_ID = s.STUDENT_ID
            WHERE e.DATE_SENT BETWEEN v_firstday AND v_lastday
              AND s.STUDENT_ID = p_student_id
            GROUP BY e.DATE_SENT, s.LASTFIRST
            ORDER BY e.DATE_SENT;

    END student_email_history;

    PROCEDURE set_email_schedule (p_date       DATE,
                                  p_frequency  NUMBER)

    AS

    BEGIN

            INSERT INTO email_schedule (es_id, next_deliv_date, frequency)
                                 VALUES(seq_schedule.nextval, p_date, p_frequency);

    END set_email_schedule;

    PROCEDURE get_email_schedule (p_result  OUT sys_refcursor)

    AS

    BEGIN

            OPEN p_result FOR
            SELECT *
            FROM email_schedule
            WHERE es_id = (SELECT MAX(es_id)
                           FROM email_schedule);

    END get_email_schedule;

    PROCEDURE WEEKLY_PROGRESS_REPORT (p_result  OUT sys_refcursor,
                                      p_result1 OUT sys_refcursor,
                                      p_student_id  NUMBER)

    AS


    BEGIN

            OPEN p_result1 FOR
            SELECT s.first_name, s.last_name,
                   15 AS grade_level,
                   s.email AS banv_email,
                   x.email_stu_pers AS personal_email,
                   x.email_guard_pers AS parent_email
            FROM pa_student s LEFT JOIN pa_student_ext x ON s.student_id=x.student_id
            WHERE s.student_id = p_student_id;

            OPEN p_result FOR
            SELECT c.course_name,
                   t.first_name||'  '||t.last_name AS teacher,
                   8906 AS teacher_phone,
                   t.email_addr,
                   'B' AS grade,
                   85 AS percentage,
                   200 AS total_minutes_previous_week,
                   TO_DATE('07/30/2012','MM/DD/YYYY') AS last_login_date
            FROM pa_student s JOIN pa_enrollment e ON s.student_id = e.student_id
                              JOIN pa_section se ON e.section_id = se.section_id
                              JOIN pa_teacher t ON se.teacher_id = t.teacher_id
                              JOIN pa_course c ON se.course_id = c.course_id
            WHERE s.student_id = p_student_id
            ORDER BY c.course_name;


    END weekly_progress_report;

    PROCEDURE ADD_WPR_EMAIL (p_student_id   NUMBER,
                             p_body         BLOB,
                             p_id    OUT    NUMBER)

    AS

    BEGIN

            INSERT INTO WPR_EMAIL (id, student_id, body)
            VALUES(seq_email.nextval, p_student_id, p_body)
            RETURNING id INTO p_id;


    END add_wpr_email;

    PROCEDURE ADD_WPR_DETAILS (p_id      NUMBER,
                               p_email   VARCHAR2,
                               p_type    VARCHAR2,
                               p_status  NUMBER)

AS

    BEGIN

            INSERT INTO WPR_DETAILS(wpr_id, email_address, email_type, deliv_status)
            VALUES(p_id, p_email, p_type, p_status);

            IF p_status=1 THEN
                UPDATE WPR_EMAIL
                SET deliv_status = 1
                WHERE ID = p_id;
            END IF;

    END add_wpr_details;

    PROCEDURE WEEKLY_PROGRESS_REPORT (p_students  OUT sys_refcursor,
                                      p_grades    OUT sys_refcursor)
    AS
    BEGIN
        OPEN p_students FOR
            SELECT s.student_id,
                   s.first_name,
                   s.last_name,
                   10 AS grade_level,
                   s.email AS banv_email,
                   x.email_stu_pers AS personal_email,
                   x.email_guard_pers AS parent_email
            FROM pa_student s LEFT JOIN pa_student_ext x ON s.student_id=x.student_id
            WHERE s.student_id IN (SELECT student_id
                                   FROM pa_enrollment
                                   WHERE semester_id=PKG_SEMESTER.semester_by_date)
            ORDER BY 1;

            OPEN p_grades FOR
            SELECT s.student_id,
                   c.course_name,
                   t.first_name||'  '||t.last_name AS teacher,
                   8906 AS teacher_phone,
                   t.email_addr,
                   'B' AS grade,
                   85 AS percentage,
                   200 AS total_minutes_previous_week,
                   TO_DATE('07/30/2012','MM/DD/YYYY') AS last_login_date
            FROM pa_student s JOIN pa_enrollment e ON s.student_id = e.student_id
                              JOIN pa_section se ON e.section_id = se.section_id
                              JOIN pa_teacher t ON se.teacher_id = t.teacher_id
                              JOIN pa_course c ON se.course_id = c.course_id
            WHERE s.student_id IN (SELECT student_id
                                   FROM pa_enrollment
                                   WHERE semester_id=PKG_SEMESTER.semester_by_date)
            ORDER BY 1,2;
    END weekly_progress_report;
END pkg_bb;
